package ch.hearc.infrarouge.tools;

import static ch.hearc.infrarouge.preferences.Preferences.connectionString;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import ch.hearc.infrarouge.models.Forum;
import ch.hearc.infrarouge.models.ForumThread;
import ch.hearc.infrarouge.models.Reply;
import ch.hearc.infrarouge.models.ThreadMessage;
import ch.hearc.infrarouge.models.User;
import ch.hearc.infrarouge.preferences.Preferences;

public class DatabaseTools {

	public static void insertUsers(List<User> users)
			throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try {
			connection = DriverManager
					.getConnection(Preferences.connectionString);

			String tableName = "user";
			String preparedQuery = "INSERT INTO " + tableName + " VALUES(?, ?)";
			PreparedStatement statement = connection
					.prepareStatement(preparedQuery);

			for (User user : users) {
				statement.setInt(1, (int) user.getId());
				statement.setString(2, user.getName());

				statement.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public static void insertForums(List<Forum> forums)
			throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try {
			connection = DriverManager
					.getConnection(Preferences.connectionString);

			String tableName = "forum";
			String preparedQuery = "INSERT INTO " + tableName
					+ " VALUES(null, ?, ?)";
			PreparedStatement statement = connection
					.prepareStatement(preparedQuery);

			String colName = "last_id";
			String selectQuery = "SELECT LAST_INSERT_ROWID() as last_id FROM "
					+ tableName;
			Statement statement2 = connection.createStatement();

			for (Forum forum : forums) {
				statement.setString(1, forum.getTitle());
				statement.setString(2, forum.getDescription());

				statement.execute();

				// update forum id
				ResultSet resultSet = statement2.executeQuery(selectQuery);
				int lastInsertId = resultSet.getInt(colName);
				forum.setId(lastInsertId);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public static void insertForumThreads(List<ForumThread> forumThreads)
			throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try {
			connection = DriverManager
					.getConnection(Preferences.connectionString);

			String tableName = "forumthread";
			String preparedQuery = "INSERT INTO " + tableName
					+ " VALUES(null, ?, ?, ?, ?)";
			PreparedStatement statement = connection
					.prepareStatement(preparedQuery);

			String colName = "last_id";
			String selectQuery = "SELECT LAST_INSERT_ROWID() as last_id FROM "
					+ tableName;
			Statement statement2 = connection.createStatement();

			for (ForumThread forumThread : forumThreads) {
				statement.setString(1, forumThread.getTitle());
				statement.setString(2, forumThread.getUrl());
				statement.setInt(3, forumThread.getForum().getId());
				statement.setInt(4, (int) forumThread.getAuthor().getId());

				statement.execute();

				// update forumthread id
				ResultSet resultSet = statement2.executeQuery(selectQuery);
				int lastInsertId = resultSet.getInt(colName);
				forumThread.setId(lastInsertId);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public static void insertThreadMessages(List<ThreadMessage> threadMessages)
			throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try {
			connection = DriverManager.getConnection(connectionString);

			String tableName = "threadmessage";
			String preparedQuery = "INSERT INTO " + tableName
					+ " VALUES(?, ?, ?, ?, ?, ?)";
			PreparedStatement statement = connection
					.prepareStatement(preparedQuery);

			for (ThreadMessage threadMessage : threadMessages) {
				statement.setInt(1, (int) threadMessage.getMessageId());
				statement.setString(2, threadMessage.getMessage());
				statement.setInt(3, threadMessage.getForumThread().getId());
				statement.setInt(4, (int) threadMessage.getAuthor().getId());
				statement.setLong(5, threadMessage.getTimestamp());
				statement.setInt(6, -1); // we do not know the polarity yet so we say -1

				statement.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public static void insertReplies(List<Reply> replies)
			throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try {
			connection = DriverManager
					.getConnection(Preferences.connectionString);

			String tableName = "reply";
			String preparedQuery = "INSERT INTO " + tableName
					+ " VALUES(?, ?, ?)";
			PreparedStatement statement = connection
					.prepareStatement(preparedQuery);

			for (Reply reply : replies) {
				statement.setInt(1, (int) reply.getReplyFrom().getId());
				statement.setInt(2, (int) reply.getReplyTo().getId());
				statement.setInt(3, (int) reply.getThreadMessage()
						.getMessageId());

				statement.execute();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

}
